<?php

include_once "src/controller/dbmanagement/DBConnection.php";
include_once "src/rrsexception/DBConnectionException.php";
include_once "src/rrsexception/DBException.php";
include_once "src/model/User.php";


/**
 * It includes all the functions for the management of the data related to the
 * user information.
 */
class UserDBManagement {

    /**
     *  Returns User Object with user information in it if the user table contains a
     *  user with the User Id of $userId.
     */
    public static function findByUserId($userId) {
        try {
            $cnn = new DBConnection();
            $user = new User();
            $query = "SELECT * FROM tUser WHERE tUser.User_id = '$userId'";
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num==0) //user not in database
            {
                //print"user ID $userId is not in database\n";
                $cnn->disconnect();
                return FALSE;
            }
            //$userId  = mysql_result($result,0,"User_id");
            $userName = mysql_result($result,0,"Username");
            $userPassword = mysql_result($result,0,"Password");
            $userEmail = mysql_result($result,0,"Email");
            $userType = mysql_result($result,0,"User_type_id");
            $user->setAll($userName, $userType, $userPassword, $userEmail);
            $user->setUserId($userId);
            $cnn->disconnect();
            return $user;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }

    }

    /**
     *  Returns User Object with user information in it if the user table contains a
     *  user with the login ID of $email.
     */

    public static function findByLoginId($email) {
        try {
            $cnn = new DBConnection();
            $user = new User();
            $email= $cnn->escapeString($email);
            $query = "SELECT * FROM tUser WHERE Email = '$email'";
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num==0) //user not in database
            {
                $cnn->disconnect();
                return FALSE;
            }
            $userId  = mysql_result($result,0,"User_id");
            $userName = mysql_result($result,0,"Username");
            $userPassword = mysql_result($result,0,"Password");
            $userEmail = mysql_result($result,0,"Email");
            $userType = mysql_result($result,0,"User_type_id");
            $user->setAll($userName, $userType, $userPassword, $userEmail);
            $user->setUserId($userId);
            $cnn->disconnect();
            return $user;
        } catch (DBException $e) {
            throw $e;
        } catch (DBConnectionException $e) {
            throw $e;
        }

    }

    /**
     *   Deletes the user with User_id of $userId from the database.
     *   Parameter $userId is the User_id of the user to be deleted.
     *   Returns true if transaction is complete and false if user does
     *   not exist.
     */
    public static function deleteByUserId($userId) {
        try {
            $cnn = new DBConnection();
            $cnn->execute("START TRANSACTION");
            $query = "SELECT * FROM tUser WHERE User_id = '$userId'";
            //Verify user exists
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num != 1)
            {
               $cnn->execute("ROLLBACK");
               $cnn->disconnect();
               return FALSE;
            }
            $query = "DELETE FROM tUser WHERE User_id = '$userId'";
            $result = $cnn->execute($query);
            $cnn->execute("COMMIT");
            $cnn->disconnect();
            return TRUE;
        }
        catch (DBException $e) {
            throw $e;
        }
        catch (DBConnectionException $e) {
            throw $e;
        }
    }

    /**
     * Returns array indexed by userName.
     * Returns empty array if there are no users.
     */
    public static function findAllUsers() {
        try {
            $cnn = new DBConnection();
            $userArray = array();
            $query = "SELECT * FROM tUser"; // WHERE tUser.User_type_id <> 1
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            $i=0;
            while ($i < $num) {
                $user = new User();
                $userId  = mysql_result($result,$i,"User_id");
                $userName = mysql_result($result,$i,"Username");
                $userPassword = mysql_result($result,$i,"Password");
                $userEmail = mysql_result($result,$i,"Email");
                $userType = mysql_result($result,$i,"User_type_id");
                $user->setAll($userName, $userType, $userPassword, $userEmail);
                $user->setUserId($userId);
                $userArray[] = $user;
                $i++;
            }

            $cnn->disconnect();
            return $userArray;
        }
        catch (DBException $e) {
            throw $e;
        }
        catch (DBConnectionException $e) {
            throw $e;
        }

    }

    /**
     *   Updates a user with th data in $user as identified by the User
     *   the User ID in $user.
     *   Returns true if transaction is complete and false if user does
     *   not exist or the new password is already taken.
     *   Throws DBExeption if update is unsuccessful.
     */
    public static function updateUserAccount($user) {
        try {
            $cnn = new DBConnection();
            $userId  = $user->getUserId();
            $userName = $cnn->escapeString($user->getName());
            $userPassword = $cnn->escapeString($user->getPswd());
            $userEmail = $cnn->escapeString($user->getEmail());
            $userType = $user->getType();
            $cnn->execute("START TRANSACTION");
            $query = "SELECT * FROM tUser WHERE Email = '$userEmail' AND User_id != '$userId'";
            //Verify user did not change email to one already in use
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num != 0)
            {
               $cnn->execute("ROLLBACK");
               $cnn->disconnect();
               return FALSE;
            }
            $query = "SELECT * FROM tUser WHERE User_id = '$userId'";
            //Verify user exists
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num != 1)
            {
               $cnn->execute("ROLLBACK");
               $cnn->disconnect();
               return FALSE;
            }
            $query = "UPDATE tUser SET Username = '$userName', Password = '$userPassword', Email = '$userEmail', User_type_id = '$userType' WHERE User_id = '$userId'";
            $result = $cnn->execute($query);
            $cnn->execute("COMMIT");
            $cnn->disconnect();
            return TRUE;
        }
        catch (DBException $e) {
            $cnn->execute("ROLLBACK");
            throw $e;
        }
        catch (DBConnectionException $e) {
            throw $e;
        }
    }

    /**
     *   Updates a user with th data in $user as identified by the User
     *   the User ID in $user.
     *   Throws DBExeption if insert is unsuccessful.
     *   return True if transaction is complete False if user already exists
     */
    public static function insertUserAccount($user) {
        try {
            $cnn = new DBConnection();
            //$userId  = $user->getUserId();
            $userName = $cnn->escapeString($user->getName());
            $userPassword = $cnn->escapeString($user->getPswd());
            $userEmail = $cnn->escapeString($user->getEmail());
            $userType = $user->getType();
            $cnn->execute("START TRANSACTION");
            $query = "SELECT * FROM tUser WHERE Email = '$userEmail'";
            $result = $cnn->execute($query);
            $num = mysql_numrows($result);
            if ($num!=0)
            {
               $cnn->execute("ROLLBACK");
               $cnn->disconnect();
               return FALSE;
            }
            $query = "INSERT INTO tUser (UserName,Password,Email,User_type_id) VALUES ('$userName','$userPassword','$userEmail','$userType')";
            $result = $cnn->execute($query);
            $cnn->execute("COMMIT");
            $cnn->disconnect();
            return TRUE;
        }
        catch (DBException $e) {
            $cnn->execute("ROLLBACK");
            throw $e;
        }
        catch (DBConnectionException $e) {
            throw $e;
        }
    }

}
?>
